package lzj.tools;


import com.jfinal.kit.StrKit;
import java.util.ArrayList;
import java.util.List;

/**
 * @author y
 * @create 2018-01-19 0:13
 * @desc
 **/
public class ExcelReaderUtil {

  //excel2003扩展名
  public static final String EXCEL03_EXTENSION = ".xls";
  //excel2007扩展名
  public static final String EXCEL07_EXTENSION = ".xlsx";

  static List<String> sqllist = new ArrayList<>();
  static int totalRows = 0;

  /**
   * 每获取一条记录，即打印 在flume里每获取一条记录即发送，而不必缓存起来，可以大大减少内存的消耗，这里主要是针对flume读取大数据量excel来说的
   */
  public static void sendRows(String filePath, String sheetName, int sheetIndex, int curRow,
      List<String> cellList) {
/*    StringBuffer oneLineSb = new StringBuffer();
    oneLineSb.append(filePath);
    oneLineSb.append("--");
    oneLineSb.append("sheet" + sheetIndex);
    oneLineSb.append("::" + sheetName);//加上sheet名
    oneLineSb.append("--");
    oneLineSb.append("row" + curRow);
    oneLineSb.append("::");
    for (String cell : cellList) {
      oneLineSb.append(cell.trim());
      oneLineSb.append("|");
    }
   String oneLine = oneLineSb.toString();
    if (oneLine.endsWith("|")) {
      oneLine = oneLine.substring(0, oneLine.lastIndexOf("|"));
    }*/// 去除最后一个分隔符


    //System.out.println(oneLine);
    String sql = "INSERT IGNORE INTO  `code`( `shbzhm`, `name`, `start`, `end`,`date`, `jine`) values(";


    if (StrKit.notBlank(cellList.get(0).trim())&& StrKit.notBlank(cellList.get(1).trim())&& StrKit
        .notBlank(cellList.get(2).trim())&& StrKit.notBlank(cellList.get(3).trim())&& StrKit
        .notBlank(cellList.get(4).trim())) {
      sql += " '" + cellList.get(0).trim() + "', ";

      sql += " '" + cellList.get(1).trim() + "' ,";
      String start1 = cellList.get(2).trim();
      sql += " " + Integer.parseInt(start1) + " ,";
      String end1 = cellList.get(3).trim();
      sql += " " + Integer.parseInt(end1) + " ,";
      String date1 = cellList.get(4).trim();
      sql += " '" + date1 + "' ,";
      sql += " " + Double.parseDouble(cellList.get(5).trim()) + " )";
      sqllist.add(sql);
    }else {
      System.out.println(cellList.get(0).trim());
    }
  }

  public static void readExcel(String fileName) throws Exception {
    totalRows = 0;
    if (fileName.endsWith(EXCEL03_EXTENSION)) { //处理excel2003文件
//      ExcelXlsReader excelXls=new ExcelXlsReader();
//      totalRows =excelXls.process(fileName);
    } else if (fileName.endsWith(EXCEL07_EXTENSION)) {//处理excel2007文件
      ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
      totalRows = excelXlsxReader.process(fileName);
    } else {
      throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
    }
    System.out.println("发送的总行数：" + totalRows);
  }

  public static void main(String[] args) throws Exception {
    String path = "d:\\test1.xlsx";
    ExcelReaderUtil.readExcel(path);
  }


  /**
   * 外部指定接口
   */
  public static List<String> ExcelSql(String path)   {
    try{
    sqllist = new ArrayList<>();

    ExcelReaderUtil.readExcel(path);
    if (totalRows == sqllist.size()) {
      System.out.println("正好：" + totalRows);
    } else {
      System.out.println("不正好：" + totalRows);
    }

    }catch (Exception e){
      e.printStackTrace();
    }
    return sqllist;
  }
}